import geopandas as gpd
import pandas as pd
from geopandas import GeoDataFrame
from sqlalchemy import create_engine
from geopandas.geoseries import GeoSeries
from shapely.wkt import loads
from shapely.geometry import LineString, Point, Polygon


class Geo(object):

    def __init__(self):
        self._conn = self.get_oracle_engine()
        self.earth_R = 6371.393

    def test(self):
        gps = self.get_oracle_data(None,None)
        points = gps.loc[:,['longitude','latitude']]

        # points.loc[:,'Coordinates'] = list(zip(points.longitude, points.latitude))
        # points.loc[:,'Coordinates'] = points['Coordinates'].apply(Point)
        line = LineString(points[['longitude','latitude']].values)
        pl = self.data()
        re = pl.geometry.intersects(line.buffer(0.1/self.earth_R))
        places = pl[re==False]['NAME_CHN'].drop_duplicates()



        # shp = fiona.open('ZIPCODES_NT_POLY.shp', 'r')
        # p_in = pyproj.Proj(shp.crs)
        # bound_box = box(*shp.bounds)
        # shp.close()
        #
        # p_out = pyproj.Proj({'init': 'EPSG:4326'})  # aka WGS84
        # project = partial(pyproj.transform, p_in, p_out)
        #
        # bound_box_wgs84 = transform(project, bound_box)

        print('')

    def data(self):
        # gispl = ['R_GS_PL','R_GD_PL','R_SD_PL','R_XD_PL','R_CSKSL_PL','R_CSZGD_PL','R_CSCGD_PL','R_CSPTDL_PL','R_SNHT_PL']
        gispl = ['R_GS_PL', 'R_GD_PL','R_SD_PL','R_CSZGD_PL']
        array = []
        for plstr in gispl:
            pl = gpd.read_file("E:\EHL\项目\现场\石家庄\资源\hbgis/"+plstr+"/"+plstr+".shp")
            array.append(pl)
        frame = pd.concat(array)
        # frame.info(memory_usage='deep')
        return frame

    def get_oracle_data(self,start,end):
        df = pd.read_sql_query(
            """select * from t_ew_gps t where t.plate_num='冀JV1276' order by t.up_time """,
            # """ select * from t_ew_gps where  up_time>= to_date('%s' ,'yyyy-MM-dd hh24:mi:ss')
            # AND up_time<= to_date('%s' ,'yyyy-MM-dd hh24:mi:ss') """ % (start, end),
            self._conn
        )
        return df

    def get_oracle_engine(self):
        user = 'ehl_test'
        password = 'ehl1234'
        ip = '10.2.111.161'
        port = '1521'
        instance = 'orcl'
        engine_str = 'oracle://%s:%s@%s:%s/%s' % (user, password, ip, port, instance)
        engine = create_engine(engine_str, encoding='utf-8')
        return engine

    def test_sjoin(self):
        polys1 = GeoSeries(
            [Point(0,0),
             Point(1,1),
             Point(1.3,1)])

        polys2 = GeoSeries(
            [Point(0,0),
             Point(1,1.1),
             Point(1.2,1)])

        df1 = GeoDataFrame({'geometry': polys1, 'df1': [0, 1, 2]})
        df2 = GeoDataFrame({'geometry': polys2, 'df2': [3, 4, 5]})
        df1.geometry = df1.buffer(1)
        s = gpd.sjoin(df1,df2,op='contains')
        print('ok')


if __name__ == '__main__':
    geo = Geo()
    # geo.test()
    geo.test_sjoin()